﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Configuration;
using CsharpTrainer.Linq;
using CsharpTrainer.Linq.Model;
using Zeus.Thunder.Core;

namespace SharpTrainer.Linq
{
    class LinqJoinCompare : ITestCase
    {
        public void Run()
        {
            InnerJoinTest();

            GroupJoinTest();

            LeftJoinTest();
        }

        private static void InnerJoinTest()
        {
            using (DataContext context = new DataContext(ConfigurationManager.ConnectionStrings["NorthwindConnStr"].ToString()))
            {
                using (NorthwindDBDataContext northwindContext = new NorthwindDBDataContext())
                {

                    var query = from c in northwindContext.Customers
                                join o in northwindContext.Orders
                                on c.CustomerID equals o.CustomerID
                                select new CustomerOrder
                                {
                                    CompanyName = c.CompanyName,
                                    ContactName = c.ContactName,
                                    ContactTitle = c.ContactTitle,
                                    OrderID = o.OrderID,
                                    OrderDate = (DateTime)o.OrderDate
                                };
                    Console.WriteLine("Customer Order Inner Join Count: {0}", query.Count());
                }
            }
        }

        private static void GroupJoinTest()
        {
            using (DataContext context = new DataContext(ConfigurationManager.ConnectionStrings["NorthwindConnStr"].ToString()))
            {
                using (NorthwindDBDataContext northwindContext = new NorthwindDBDataContext())
                {

                    var query = from c in northwindContext.Customers
                                join o in northwindContext.Orders
                                on c.CustomerID equals o.CustomerID
                                into custOrders
                                select new
                                {
                                    c.CompanyName,
                                    c.ContactName,
                                    c.ContactTitle,
                                    custOrders
                                };
                    Console.WriteLine("Customer Order Group Join Count: {0}", query.Count());
                }
            }
        }

        private static void LeftJoinTest()
        {
            using (DataContext context = new DataContext(ConfigurationManager.ConnectionStrings["NorthwindConnStr"].ToString()))
            {
                using (NorthwindDBDataContext northwindContext = new NorthwindDBDataContext())
                {

                    var query = from c in northwindContext.Customers
                                join o in northwindContext.Orders
                                on c.CustomerID equals o.CustomerID
                                into custOrders
                                from co in custOrders.DefaultIfEmpty()
                                select new CustomerOrder
                                {
                                    CompanyName = co.Customers.CompanyName,
                                    ContactName = co.Customers.ContactName,
                                    ContactTitle = co.Customers.ContactTitle,
                                    OrderID = co.OrderID,
                                    OrderDate = (DateTime)co.OrderDate
                                };
                    Console.WriteLine("Customer Order Left Join Count: {0}", query.Count());
                }
            }
        }
    }
}
